<?php

PutEnv("ORACLE_HOME=/usr/local/dbpackages/oracle");

//$c = oci_connect('comp231stu116', '47600210', 'comp231.cse.ust.hk');
$c = oci_connect('comp231stu032', 'chemistry', 'comp231.cse.ust.hk');

if(!$c) {
	echo "<p>Unable to connect:" . var_dump(oci_error($c));
	die();
}

//forming teams ---------------------------------------------------------------
	//update all value team_id = null in table flight

	$s = "update flight set team_id=NULL";
	$q = oci_parse($c, $s);
	oci_execute($q);

	//delete all record from form_1_team
	$s = "delete from form_1_team";
	$q = oci_parse($c,$s);
	oci_execute($q);

	//delete all record from form_2_team
	$s = "delete from form_2_team";
	$q = oci_parse($c,$s);
	oci_execute($q);

	//delete all record from team;
	$s = "delete from team";
	$q = oci_parse($c,$s);
	oci_execute($q);

$team_id = 1;
for ($i = 0; $i <3; $i++) {
//get aviliable driver IDs
	$wts = array('MA','AA','EA');
	$sd = "select driver_ID from driver, staff where driver_ID = ID and working_time_slot = '" .$wts[$i]. "' and working_status = 'AV' and  not exists (select driver_ID from form_2_team where form_2_team.driver_ID = driver.driver_ID)";
//get aviliable non-driver IDs
	$sn = "select nondriver.ID from nondriver, staff where nondriver.ID = staff.ID and working_time_slot = '" .$wts[$i]. "' and working_status = 'AV' and not exists (select staff_ID from form_1_team where staff_ID = nondriver.ID)";
	$qd = oci_parse($c,$sd);
	$qn = oci_parse($c,$sn);
	
	//echo $sd. "<br>";
	//echo $sn. "<br>";
	oci_execute($qd);
	oci_execute($qn);

	//store driver IDs to array $dataD
	//the size $countD
	$countD = 0;
	while ($rowD = oci_fetch_array($qd, OCI_BOTH)) {
		$dataD[$i][] = $rowD;
		$countD++;
	}
	
	//store non-driver IDs to array $dataN
	//the size $countN
	$countN = 0;
	while ($rowN = oci_fetch_array($qn, OCI_BOTH)) {
		$dataN[$i][] = $rowN;
		$countN++;
	}
//	echo $sn. "<br>";
//	echo $countN. "<br>";

	//to form teams and insert the records to corresponding tables (team, form_1_team and form_2_team)
	$num = $countD < $countN ? $countD : $countN;
	for ($j = 0; $j < $num; $j++) {
		$s = "insert into team values (" .$team_id. ")";
		$q = oci_parse($c,$s);
		oci_execute($q);
		
		$s = "insert into form_1_team values(" .$dataN[$i][$j][0]. "," .$team_id. ")";
		$q = oci_parse($c,$s);
		oci_execute($q);
		
		$s = "insert into form_2_team values(" .$dataD[$i][$j][0]. "," .$team_id. ")";
		$q = oci_parse($c,$s);
		oci_execute($q);

	//c	echo "team " .$team_id." ". $dataN[$i][$j][0]. " ".$dataD[$i][$j][0]. "<br>";
		$team_id++;


	}

	//if driver is more than non-driver
	//two drivers form a team
	if ($num == $countN) {
		for($j = 0; $j < ($countD - $num)/2; $j++) {
			$s = "insert into team values (" .$team_id. ")";
			$q = oci_parse($c,$s);
			oci_execute($q);

			$s = "insert into form_1_team values(" .$dataD[$i][$j*2 + $num][0]. "," .$team_id. ")";
			$q = oci_parse($c,$s);
			oci_execute($q);

			$s = "insert into form_2_team values(" .$dataD[$i][$j*2 + $num+1][0]. "," .$team_id. ")";
		//	echo "team " .$team_id." ". $dataD[$i][$j*2+$num][0]." ". $dataD[$i][$j*2+$num+1][0]. "<br>";
			$team_id++;
		}
	}
}	//end of for loop

//end of forming teams

//----------------generate the roster -------------

	$s = "drop table schedule";
	$q = oci_parse($c,$s);
	oci_execute($q);

	//create a table to store today work schedule
	$s = "create table schedule (team_id number(3), airline_code varchar2(2), flight_id number(4), service_type varchar2(1), time_from varchar2(8), time_to varchar2(8))";
	//$s = "create GLOBAL TEMPORARY table schedule (team_id number(3), airline_code varchar2(2), flight_id number(4), service_type varchar2(1), time_from varchar2(8), time_to varchar2(8)) ON COMMIT PRESERVE ROWS";
	$q = oci_parse($c, $s);
	oci_execute($q);
//	echo $s."<br>";

	
	//find the flight that we need to serve
	//$s = "select flight_id, flight_type,to_date(STA, 'YYYY-MM-DD:HH24:MI:SS'), to_date(LCT, 'YYYY-MM-DD:HH24:MI:SS'),server_time,food_uploading_time airline_id from flight, aircraft where flight.AC_type = aircraft.AC_type order by LCT, STA";
	$s = "select flight_id, flight_type,STA, LCT,server_time,food_uploading_time, airline_code from flight, aircraft where flight.AC_type = aircraft.AC_type order by LCT, STA";
	$q = oci_parse($c,$s);
//	echo $s ."<br>";
	oci_execute($q);

	//assign the team to server untill the list is empty/
	//f-id, f-type, STA, LCT, G-time, Ftime, airline-id
	while ($flightR = oci_fetch_array($q,OCI_BOTH)) {
		$total_ST = 0;
		switch ($flightR[1]) {
			case ("I"):	$total_ST = $flightR[4];break;
			case ("T"):	$total_ST = $flightR[4] + $flightR[5];break;
			case ("O"):	$total_ST = $flightR[5];break;
		}
		$tFID = $flightR[0];
		$tACT = $flightR[1];
		$tSTA = strtotime(substr($flightR[2],11,19));
		$tLCT = strtotime(substr($flightR[3],11,19));
		$tGST = $flightR[4];
		$tFST = $flightR[5];
		$tACC = $flightR[6];
		//echo "$tFID $tACT $tSTA $tLCT $tGST $tFST $tACC<br>";
		
		
		$r = "select team_ID from team where not exists (select * from schedule where team.team_id = schedule.team_id)";
		$rr = oci_parse($c,$r);
		oci_execute($rr);
		
		$result = false;
		$reslutDate;
		
		//if a team has no job yet
		if($row = oci_fetch_array($rr,OCI_BOTH)){
			$temp1 = "select working_time_slot from staff, form_1_team where team_id =" .$row[0]. " and staff_id = id";
			$temp2 = oci_parse($c,$temp1);
//echo $temp1 ."<br>";
			oci_execute($temp2);
			$temp3 = oci_fetch_array($temp2, OCI_BOTH);
		
			$startT = 0;	
			switch ($temp3[0]) {
				case "MA":	$startT = strtotime("05:00:00");
						 break;
				case "AA":	$startT = strtotime("12:00:00");
						 break;
				case "EA":	$startT = strtotime("15:00:00");
						 break;
			}
			//echo date('H:i:s', $startT)."<br>";
			$result = true;
			if ($tSTA)
				$resultData = $tSTA;
			else
				$resultData = $startT;
			
		//	echo date('H:i:s', $startT);
			$temp1 = "update flight set team_id = " . $row[0]. " where flight_id = $tFID and airline_code='$tACC'";
			$temp2 = oci_parse($c,$temp1);
		//	echo $temp1."<br>";
			oci_execute($temp2);

			switch ($tACT) {
				case "I":	$insertR = "insert into schedule values (" .$row[0]. ",'$tACC', $tFID, 'G','" 
							.date('H:i:s',$resultData)."','" .date('H:i:s', $resultData+$total_ST*60). "')";
						break;

				case "T":	$insertR = "insert into schedule values (" .$row[0]. ", '$tACC', $tFID, 'G','" 
							.date('H:i:s',$resultData)."','" .date('H:i:s', $resultData+$tGST*60). "')";
						$runInsertR = oci_parse($c, $insertR);
						oci_execute($runInsertR);						
						
						$insertR = "insert into schedule values (" .$row[0]. ", '$tACC', $tFID, 'F','" 
							.date('H:i:s',$resultData+$tGST*60)."','" .date('H:i:s', $resultData+$total_ST*60). "')";
						break;
				case "O":	$insertR = "insert into schedule values (" .$row[0]. ", '$tACC', $tFID, 'F','" .date('H:i:s',$resultData)
							."','" .date('H:i:s', $resultData+$total_ST*60). "')";
						break;
			}
			$runInsertR = oci_parse($c, $insertR);
			oci_execute($runInsertR);
		//	echo $insertR ."<br>";
		}
		
		if (!$result) {		
			$r = "select team_ID, count(*) as num from schedule group by team_ID order by num";
			//echo $r ."<br>";
			$rr = oci_parse($c,$r);
			oci_execute($rr);
		}

		

		//sort the teams by number of jobs
		while(!result && $teamV = oci_fetch_array($rr, OCI_BOTH)) {
			$fts = "select from, to from schedule where team_ID =" .$teamV[0];
			$qts = oci_parse($c,$fts);
			oci_execute($qts);
						
			//find time slot of the team
			if ($teamS = oci_fet_array($qts, OCI_BOTH) ) {
				$tmpFrom = strtotime($teamS[0]);
				$tmpTo = strtotime($teamS[1]);

				while($teamS = oci_fetch_array($qts, OCI_BOTH) ) {
					$nFrom = strtotime($teamS[0]);
					$nTo = strtotime($teamS[1]);
					$TSS = ($tmpTo - $nFrom)/60;			
					
					//if team has aviliable time slot
					if($TTS > $total_ST && $tmpTo > $tSTA) {
						$result = true;
						$resultData = $tmpTo;		//store the start time for serving the flight
						break;
					}
					$tmpFrom = $teamS[0];
					$tmpTo = $teamS[1];
				}
				
				//if no more record;
				if(!$result) {
					$temp1 = "select working_time_slot from staff, form_1_team where team_id =".$teamV[0]. " and staff_id = id";
					$temp2 = oci_parse($c, $temp1);
					oci_execute($temp2);
					$temp3 = oci_fetch_array($temp2, OCI_BOTH);

					//get end of working time slot
					switch ($temp3[0]) {
						case "MA":	$endT = strtotime("14:00"); break;
						case "AA":	$endT = strtotime("21:00"); break;
						case "EA":	$endT = strtotime("24:00"); break;
					}
					$TTS = ($tmpTo - $endT)/60;
					if ($TTS > $total_ST && $tmpTo > $tSTA && $endT < $tLCT) {
						$result = true;
						$resutlData = $tmpTo; 		//store the start time for serving the flight
					}
				}
				
			}
			

			if ($result) {
				$temp1 = "update flight set team_id = " . $row[0]. " where flight_id = $tFID and airline_code='$tACC'";
				$temp2 = oci_parse($c,$temp1);
				oci_execute($temp2);

				switch ($tACT) {
					case "I":	$insertR = "insert into schedule values (" .$row[0]. ",'$tACC', $tFID, 'G','" 
								.date('H:i:s',$resultData)."','" .date('H:i:s', $resultData+$total_ST*60). "')";
							break;

					case "T":	$insertR = "insert into schedule values (" .$row[0]. ", '$tACC', $tFID, 'G','" 
								.date('H:i:s',$resultData)."','" .date('H:i:s', $resultData+$tGST*60). "');";
							$runInsertR = oci_parse($c, $insertR);
							oci_execute($runInserR);
							$insertR = "insert into schedule values (" .$row[0]. ", '$tACC', $tFID, 'F','" 
								.date('H:i:s',$resultData+$tGST*60)."','" .date('H:i:s', $resultData+$total_ST*60). "')";
							break;
					case "O":	$insertR = "insert into schedule values (" .$row[0]. ", '$tACC', $tFID, 'F','" .date('H:i:s',$resultData)
								."','" .date('H:i:s', $resultData+$total_ST*60). "')";
							break;
				}
				$runInsertR = oci_parse($c, $insertR);
				//echo $insertR ."<br>";
				oci_execute($runInsertR);
			}
			
		}

	}
//end of generating roster

//print the result;
	echo "<h2>Welcome to the 231Chefs Roster Generation System<br></h2>";

	$s = "select team_id from team";
	$q = oci_parse($c,$s);
	oci_execute($q);


	//count MM ------------------
	$totalMM = 0;
	//---------------------------

	while($row = oci_fetch_array($q, OCI_BOTH)) {
//show the details of team------------------------------
		$tmpTID = $row[0];
		$ss = "select surname, given_name, working_time_slot, reward_per_half_hr
			from staff, form_1_team where team_id = $tmpTID and ID = staff_id";
		$qq = oci_parse($c,$ss);
		oci_execute($qq);

		oci_fetch($qq);
		$rphha = oci_result($qq,'REWARD_PER_HALF_HR');		//for count MM
		echo "Team $tmpTID :" .oci_result($qq, 'SURNAME'). " " .oci_result($qq,"GIVEN_NAME").", ";
	
		$ss = "select surname, given_name, working_time_slot, reward_per_half_hr
			 from staff, form_2_team where team_id = $tmpTID and ID = driver_id";
		$qq = oci_parse($c,$ss);
		oci_execute($qq);
		oci_fetch($qq);
		$rphhb = oci_result($qq,'REWARD_PER_HALF_HR');		//for count MM

		$wts = oci_result($qq, 'WORKING_TIME_SLOT');
		switch ($wts) {
			case 'MA': $wts = $wts. " 05:00-14:00";break;
			case 'AA': $wts = $wts. " 12:00-21:00";break;
			case 'EA': $wts = $wts. " 15:00-24:00";break;
		}
		echo oci_result($qq,'SURNAME'). " " .oci_result($qq,'GIVEN_NAME'). "<br>";
		echo "Working time: " .$wts.  "<br>";

		$ss = "select * from schedule where team_id =" .$tmpTID;
		$qq = oci_parse($c, $ss);
		oci_execute($qq);

		$hasjob = true;
		if(!oci_fetch($qq)) {
			$hasjob = false;
			echo "The team rests today";
		}

		echo "<table border = '1'>";
		do {	
			if($hasjob) {		
				$rfrom = oci_result($qq, 'TIME_FROM');
				$rto = oci_result($qq, 'TIME_TO');
				$st = (oci_result($qq, 'SERVICE_TYPE') == 'G') ? 'Galley' : 'Food Upload';
				$alc = oci_result($qq, 'AIRLINE_CODE');
				$fid = oci_result($qq, 'FLIGHT_ID');

				// count MM ----------------
				$mmto = strtotime($rto);
				$mmto = ($mmto == strtotime('00:00:00')) ? strtotime('+1 day', $mmto) : $mmto;
				$duration = ($mmto - strtotime($rfrom))/1800;
			//	echo "<tr><td>$duration</td></tr>";
				$totalMM += $duration * ($rphha + $rphhb);
				// ------------------------

				$sss = "select ac_type, flight_type, lct, gate_id from flight, schedule
					where flight.flight_id = schedule.flight_id and flight.airline_code = schedule.airline_code";
				$qqq = oci_parse($c, $sss);
				oci_execute($qqq);
				oci_fetch($qqq);
				$act = oci_result($qqq, 'AC_TYPE');
				$ft = oci_result($qqq, 'FLIGHT_TYPE');
				$lct = substr(oci_result($qqq, 'LCT'), 11,19);
				$gid = oci_result($qqq, 'GATE_ID');
				echo "<tr><td width = '150'>$rfrom - $rto</td><td width = '100'>$st</td><td width='50'>$alc$fid</td>
					<td width='50'>$act</td><td width = '20'>$ft</td>
					<td width='120'>LCT $lct</td><td width='60'>Gate $gid</td></tr>";
			}
		} while (oci_fetch($qq));
		echo "</table><br>";
		//show the schedule ----------------------------------------
	
	}
	echo "The total salary cost for today's roster is:<br>";
	echo "HKD$".$totalMM."<br>";
	echo "---------------------------------------------------------<br>";
	echo "Click <a href = 'index.php'>here</a> to return to the Main Menu:";

?>
